package c4.application.transactions.activities;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import c4.application.transactions.Activity;
import c4.application.transactions.ActivityResult;
import c4.application.transactions.CorporateDatabase;
import c4.application.transactions.Resource;
import c4.common.Constants;

public class GetCustomerInfoActivity extends Activity {

	public GetCustomerInfoActivity(Properties props) {
		super(props);
	}

	public ActivityResult execute(Resource db, Resource noss) {
		CorporateDatabase database;
		database = (CorporateDatabase)db;
		String query = "";
		ResultSet rs;
		ActivityResult result = new ActivityResult();

		if (parameters.getProperty(Constants.PIN) != null) {
			//See if customer exists
			query = "";
				
			query += "SELECT * FROM account_information " +
					 "WHERE account_number=" + (String)parameters.getProperty(Constants.ACCOUNT_NUMBER) + 
					 " AND pin='" + (String)parameters.getProperty(Constants.PIN) + "';";
			
			rs = database.executeQuery(query);
			
			try {
			
				if (rs.next()) { //customer exists
					result.setResult(1);
					
					//iterate through and get all of the value to set in the properties of ActivityResult
					result.addProperty(Constants.ACCOUNT_NUMBER, rs.getString(Constants.ACCOUNT_NUMBER));
					result.addProperty(Constants.NAME, rs.getString(Constants.NAME));
					result.addProperty(Constants.DOB,rs.getString(Constants.DOB));
					result.addProperty(Constants.SSN, rs.getString(Constants.SSN));
					result.addProperty(Constants.STREET,rs.getString(Constants.STREET));
					result.addProperty(Constants.ZIP,rs.getString(Constants.ZIP));
					result.addProperty(Constants.CITY,rs.getString(Constants.CITY));
					result.addProperty(Constants.STATE,rs.getString(Constants.STATE));
					result.addProperty(Constants.VOICEMAIL,rs.getString(Constants.VOICEMAIL));
					result.addProperty(Constants.CALLER_ID, rs.getString(Constants.CALLER_ID));
					result.addProperty(Constants.THREE_WAY,rs.getString(Constants.THREE_WAY));
					result.addProperty(Constants.DISTANCE_PROVIDER,rs.getString(Constants.DISTANCE_PROVIDER));
					result.addProperty(Constants.PIN, (String)parameters.getProperty(Constants.PIN));
					
					String account_number = rs.getString(Constants.ACCOUNT_NUMBER);
					
					//get phone numbers
					
					query = "";
					
					query = "SELECT phone_number FROM phone_number_listing WHERE account_number=" +
							account_number;
					
					rs = database.executeQuery(query);
					
					int index = 0;
					
					while(rs.next()){
						result.addProperty("phone" + index, rs.getString(Constants.PHONE_NUMBER) );
						index++;
					}
					
					//get service requests
					query = "";
					
					query = "SELECT service_number, description, service_status " +
							"FROM service_request_log where account_number=" +
							account_number;
					
					rs = database.executeQuery(query);
					
					index = 0;
					
					while(rs.next()){
						result.addProperty("service" + index, rs.getString(Constants.SERVICE_NUMBER) + ", " + 
								rs.getString(Constants.DESCRIPTION) + "," + rs.getString(Constants.SERVICE_STATUS));
						index++;
					}
					
					//get trouble tickets
					query = "";
					
					query = "SELECT ticket_number, description, ticket_status " +
							"FROM trouble_ticket_log where account_number=" +
							account_number;
					
					rs = database.executeQuery(query);
					
					index = 0;
					
					while(rs.next()){
						result.addProperty("trouble" + index, rs.getString(Constants.TICKET_NUMBER) + ", " + 
								rs.getString(Constants.DESCRIPTION) + "," + rs.getString(Constants.TICKET_STATUS));
						index++;
					}
				}
				else { //customer doesn't exist
					result.setResult(-1);
					result.addErrorMessage("Customer not found in database. Please revalidate account number and pin \n");
				}
			} 
			catch (SQLException e) {
					e.printStackTrace();
					result.setResult(-1);
			}
		}
		else {
			result.setResult(-1);
			result.addErrorMessage("No pin was sent with this request!");
		}
		
		return result;
	}

	 
	public String getType() {
		// TODO Auto-generated method stub
		return null;
	}
}
